import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 将数据库数据缓存到本地
 */
public class PreparingCachedData {
	static Connection conn = null;
	static PreparedStatement ps = null;
	static ResultSet rs = null;

	public static void main(String[] args) throws Exception {
		File file = new File("src/BIgData/datas/sqldatas.txt");
		FileWriter fileWriter = new FileWriter(file);
		BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));


		List<IotData> datas = getDatas();
		for (IotData data : datas) {
			bufferedWriter.write(data.toString() + "\n");
		}
		bufferedWriter.close();
	}

	public static List getDatas() {
		Connection conn = getConnection();
		ArrayList<IotData> list = new ArrayList<IotData>();
		String sql = "select at,type,ds_id,value,dev_id from iot";

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				IotData iotData = new IotData();
				iotData.setAt(rs.getString("at"));
				iotData.setType(rs.getString("type"));
				iotData.setDev_id(rs.getString("dev_id"));
				iotData.setValue(rs.getString("value"));
				iotData.setDs_id(rs.getString("ds_id"));
				list.add(iotData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, ps, conn);
		}

		return list;
	}

	public static Connection getConnection() {
		Connection conn = null;

		String url = "jdbc:mysql://rm-uf6q3z5acf5pqa8l88o.mysql.rds.aliyuncs.com/blackbox?autoReconnect=true";
		String user = "yang1955316899";
		String password = "Sorry418NotFound";

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
